package Mapper.Admin;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import pojo.huiyuan;
import pojo.kechenghuanli;

import java.sql.*;
import java.time.LocalDateTime;

import static pojo.Mysql.*;

public class keChengMapper {

    /**
     * 获取数据
     * @param offset
     * @param limit
     * @return
     */
    public ObservableList<kechenghuanli> fetchDataFromDatabase(int offset, int limit) {
        ObservableList<kechenghuanli> data = FXCollections.observableArrayList();
        try (
                Connection connection = DriverManager.getConnection(url, user, dbPassword)) {
            String query = "SELECT * FROM kechengguanli LIMIT ?, ? ";
            try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
                preparedStatement.setInt(1, offset);
                preparedStatement.setInt(2, limit);

                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    while (resultSet.next()) {
                        String name = resultSet.getString("name");
                        String id = resultSet.getString("id");
                        String createtime = resultSet.getString("createtime");
                        String kechengchixushijian = resultSet.getString("kechengchixushijian");
                        String jiaolian = resultSet.getString("jiaolian");
                        String kechengfeiyong = resultSet.getString("kechengfeiyong");

                        data.add(new kechenghuanli(id, name,createtime, kechengchixushijian, jiaolian,kechengfeiyong));
                    }
                }
            }
        } catch (
                SQLException e) {
            e.printStackTrace();
        }

        return data;
    }

    public void gengxin(String id, String xingming, String huiyuanqingkuang) {
        try (Connection connection = DriverManager.getConnection(url, user, dbPassword)) {
            String query = "UPDATE kechengguanli SET name=?, kechengfeiyong=? WHERE id=?";

            try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
                preparedStatement.setString(1, xingming);
                preparedStatement.setString(2, huiyuanqingkuang);
                preparedStatement.setString(3, id);

                int rowsAffected = preparedStatement.executeUpdate();
                if (rowsAffected > 0) {
                    System.out.println("数据更新成功！");
                } else {
                    System.out.println("未找到匹配的数据或更新失败！");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public void shanchu(String id) {
        try (Connection connection = DriverManager.getConnection(url, user, dbPassword)) {
            String query = "DELETE FROM kechengguanli WHERE id = ?";

            try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
                preparedStatement.setString(1, id);

                int rowsAffected = preparedStatement.executeUpdate();
                if (rowsAffected > 0) {
                    System.out.println("数据删除成功！");
                } else {
                    System.out.println("未找到匹配的数据或删除失败！");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void tianjia(String xingming, String huiyuanqingkuang) {
        try (Connection connection = DriverManager.getConnection(url, user, dbPassword)) {
            String query = "INSERT INTO kechengguanli (name,createtime, kechengfeiyong) " +
                    "VALUES (?, ?, ?)";

            try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
                preparedStatement.setString(1, xingming);
                preparedStatement.setObject(2, LocalDateTime.now()); // 使用当前时间
                preparedStatement.setString(3, huiyuanqingkuang);

                int rowsAffected = preparedStatement.executeUpdate();
                if (rowsAffected > 0) {
                    System.out.println("数据添加成功！");
                } else {
                    System.out.println("数据添加失败！");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}
